﻿using System;
using System.Drawing;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlServerCe;
using System.Text;
using System.Windows.Forms;


namespace GraphsLab5
{
    class SQLConnection
    {
        private const string CONNECTION_STRING = "Data Source='DB.sdf';";
        private static int nextP = 0;
        private static int nextC = 0;
        private static int nextL = 0;
        private static List<List<Point> > points = new List<List<Point> >();
        private static List<List<List<int> > > lines = new List<List<List<int> > >();
        private static List<List<Color> > colors = new List<List<Color> >();

        private static void refreshList()
        {
            int count = 0;
            points.Clear();
            points.Add(new List<Point>());
            lines.Clear();
            lines.Add(new List<List<int>>());
            colors.Clear();
            colors.Add(new List<Color>());
                using (var connection = new SqlCeConnection(CONNECTION_STRING))
                {
                    using (var command = new SqlCeCommand(
                        "SELECT * FROM Graphs", connection))
                    {
                        connection.Open();
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int x = reader.GetInt32(0);
                                int y = reader.GetInt32(1);
                                if (x < 0) {
                                    count++;
                                    points.Add(new List<Point>());
                                    lines.Add(new List<List<int>>());
                                    colors.Add(new List<Color>());
                                    continue;
                                }
                                points[count].Add(new Point(x, y));
                                Color c = new Color();
                                string color = reader.GetString(2);
                                switch (color)
                                {
                                    case "Black": { c = Color.Black; break; }
                                    case "Red": { c = Color.Red; break; }
                                    case "Green": { c = Color.Green; break; }
                                    case "Blue": { c = Color.Blue; break; }
                                    default: { c = Color.Aqua; break; }
                                }
                                colors[count].Add(c);
                                string allLines;
                                List<int> add = new List<int>();
                                if (!reader.IsDBNull(3))
                                {
                                    allLines = reader.GetString(3);
                                    if (allLines.Length > 0)
                                    {
                                        foreach (string s in allLines.Split(','))
                                        {
                                            add.Add(Int32.Parse(s));
                                        }
                                    }
                                }
                                lines[count].Add(add); 
                        }
                    }
                }
            }
                points.Remove(new List<Point>());
                colors.Remove(new List<Color>());
                lines.Remove(new List<List<int>>());

        }

        public static List<Point> getPoints(){

            List<Point> list;

            if (points.Count == 0)
            {
                refreshList();    
            }
            list = new List<Point>(points[nextP]);
            nextP++;
            if (nextP >= points.Count) nextP = 0;

            return list;

        }

        public static List<Color> getColors(){

            List<Color> list;
            if (colors.Count == 0)
            {
                refreshList();
            }
            list = new List<Color>(colors[nextC]);
            nextC++;
            if (nextC >= colors.Count) nextC = 0;

            return list;

        }

        public static List<List<int> > getLines()
        {

            List<List<int> > list;

            if (lines.Count == 0)
            {
                refreshList();
            }
            list = new List<List<int> >(lines[nextL]);
            nextL++;
            if (nextL >= lines.Count) nextL = 0;

            return list;

        }


        public static bool saveDB(List<Point> _points, List<Color> _colors, List<List<int>> _lines)
        {
            /*
            try
            {

                using (var connection = new SqlCeConnection(CONNECTION_STRING))
                {
                    using (var command = new SqlCeCommand(
                    "DELETE FROM Graphs", connection))
                    {
                        connection.Open();
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read()) ;
                        }
                    }
                }
            }
            catch
            {
                MessageBox.Show("Error in DB (while deleting items)!");
            }
             */
             
            try{
                    for (int i = 0; i < _points.Count; i++)
                    {
                        string query = "INSERT INTO Graphs VALUES (" + _points[i].X + "," + _points[i].Y + ",'" + _colors[i].Name + "','";
                        if (_lines[i].Count > 0)
                        {
                            for (int j = 0; j < _lines[i].Count; j++)
                            {
                                query += _lines[i][j].ToString() + ",";
                            }
                            query = query.Remove(query.Length - 1);
                        }
                        query += "');";
                        using (var connection = new SqlCeConnection(CONNECTION_STRING))
                        {
                            using (var command = new SqlCeCommand(
                            query, connection))
                            {
                                connection.Open();
                                using (var reader = command.ExecuteReader())
                                {
                                    while (reader.Read());
                                }
                            }
                        }
                    }

                    string res = "INSERT INTO Graphs VALUES (-1, -1, '*', '*');";
                    using (var connection = new SqlCeConnection(CONNECTION_STRING))
                    {
                        using (var command = new SqlCeCommand(
                        res, connection))
                        {
                            connection.Open();
                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read()) ;
                            }
                        }
                    } 
                    //points.Clear();
                    //lines.Clear();
                    //colors.Clear();
                }

        
            catch (Exception e)
            {
                MessageBox.Show("Error in DB (while addind items)\n" + e.Message);
                return false;
            }
            refreshList();
            return true;
        }

    }
}
